﻿Public Class MASPRODUCT2
    Private IDMASPRODUCT As Integer
    Private IDMASPRODUCTTYPE As Integer
    Private PRODUCTNAME As String
    Private BPRICE As Double
    Private SPRICE As Double
    Private QTY As Integer
    Private REMARK As String
    Private USREQ As Integer
    Private CODE As String
    Private STATUS = Nothing
    Private SelectMasproduct = Nothing
    Private SelectHis = Nothing
    Private SelectHis2 = Nothing
    Dim connect As CONDB = CONDB.NewConnection
    Property SelectMasproduct_() As String
        Get
            SelectMasproduct = "SELECT `code` as 'CODE',`idmasproduct` as 'รหัส',`productname` as 'สินค้า',`bprice` as 'ราคาซื้อ',`sprice` as 'ราคาขาย',`qty` as 'จำนวน',masproducttype.`idmasproducttype`,masproducttype.`typename` as 'ประเภทสินค้า',`status` as 'สถานะการเปิดใช้งาน',`remark` FROM cpm.masproduct LEFT JOIN (SELECT `idmasproducttype`,`typename` FROM cpm.masproducttype WHERE `status` = 1) as masproducttype on masproduct.`idmasproducttype` = masproducttype.`idmasproducttype` ; "
            Return SelectMasproduct
        End Get
        Set(value As String)

        End Set
    End Property
    Property SelectHis_() As String
        Get
            SelectHis = "SELECT `hisbid` as 'รหัส',hisbuy.`product`,`productname` as 'ชื่อสินค้า',`qty` as 'จำนวน',hisbuy.`usreq`,bprice AS 'ราคารวม',masuser.`name` as 'ผู้ทำรายการ',`date` as 'วันที่'  "
            SelectHis += "FROM cpm.hisbuy  "
            SelectHis += "LEFT JOIN (SELECT `idmasproduct`,`productname` FROM cpm.masproduct WHERE `status` = 1) as masproduct ON hisbuy.`product` = masproduct.`idmasproduct` "
            SelectHis += "LEFT JOIN (SELECT `userid`,`name` FROM cpm.masuser WHERE `status` = 1) as masuser on hisbuy.`usreq` = masuser.`userid` "
            Return SelectHis
        End Get
        Set(value As String)

        End Set
    End Property
    Function SelectHisDate(ByVal date1 As Date, ByVal date2 As Date) As String
        SelectHis = "SELECT `hisbid` as 'รหัส',hisbuy.`product`,`productname` as 'ชื่อสินค้า',`qty` as 'จำนวน',hisbuy.`usreq`,bprice AS 'ราคารวม',masuser.`name` as 'ผู้ทำรายการ',`date` as 'วันที่'  "
        SelectHis += "FROM cpm.hisbuy  "
        SelectHis += "LEFT JOIN (SELECT `idmasproduct`,`productname` FROM cpm.masproduct WHERE `status` = 1) as masproduct ON hisbuy.`product` = masproduct.`idmasproduct` "
        SelectHis += "LEFT JOIN (SELECT `userid`,`name` FROM cpm.masuser WHERE `status` = 1) as masuser on hisbuy.`usreq` = masuser.`userid` "
        SelectHis += "WHERE date BETWEEN '" & date1.Year & "/" & date1.Month & "/" & date1.Day & "' AND '" & date2.Year & "/" & date2.Month & "/" & date2.Day & "' GROUP BY salemain.salemain;"
        Return SelectHis
    End Function
    Property SelectHis2_() As String
        Get
            'SelectHis2 = "SELECT `hissid` as 'รหัส',hissale.`product`,`productname` as 'ชื่อสินค้า',`qty` as 'จำนวน',hissale.`usreq`,concat(masuser.`name`,' ',masuser.`lastname`) as 'ผู้ทำรายการ',`date` as 'วันที่',`total` as 'ราคารวม'  FROM cpm.hissale "
            'SelectHis2 += "LEFT JOIN (SELECT `idmasproduct`,`productname` FROM cpm.masproduct WHERE `status` = 1) as masproduct ON hissale.`product` = masproduct.`idmasproduct` "
            'SelectHis2 += "LEFT JOIN (SELECT `userid`,`name`,`lastname` FROM cpm.masuser WHERE `status` = 1) as masuser on hissale.`usreq` = masuser.`userid`"
            SelectHis2 = "SELECT lpad(salemain.salemain,7,'0') AS 'รหัส',salemain.docdate AS 'วันที่',SUM(amount) AS 'ราคารวม',name AS 'ลูกค้า',concat(agentname,' ',agentlastname) AS 'ผู้ทำรายการ' "
            SelectHis2 += "FROM cpm.salemain LEFT JOIN cpm.sale ON salemain.SALEMAIN = sale.SALEMAIN LEFT JOIN cpm.mascustomer On mascustomer.idmascustomer = salemain.CUSID LEFT JOIN cpm.masagent ON masagent.AGENTID = salemain.AGENTID LEFT JOIN cpm.masproduct ON masproduct.idmasproduct = sale.PRODUCT "
            SelectHis2 += "GROUP BY salemain.salemain;"
            Return SelectHis2
        End Get
        Set(value As String)

        End Set
    End Property
    Function SelectHisDate2(ByVal date1 As Date, ByVal date2 As Date) As String
        'SelectHis2 = "SELECT `hissid` as 'รหัส',hissale.`product`,`productname` as 'ชื่อสินค้า',`qty` as 'จำนวน',hissale.`usreq`,concat(masuser.`name`,' ',masuser.`lastname`) as 'ผู้ทำรายการ',`date` as 'วันที่',`total` as 'ราคารวม'  FROM cpm.hissale "
        'SelectHis2 += "LEFT JOIN (SELECT `idmasproduct`,`productname` FROM cpm.masproduct WHERE `status` = 1) as masproduct ON hissale.`product` = masproduct.`idmasproduct` "
        'SelectHis2 += "LEFT JOIN (SELECT `userid`,`name`,`lastname` FROM cpm.masuser WHERE `status` = 1) as masuser on hissale.`usreq` = masuser.`userid` WHERE hissale.date BETWEEN '" & date1.Year & "/" & date1.Month & "/" & date1.Day & "' AND '" & date2.Year & "/" & date2.Month & "/" & date2.Day & "';"
        SelectHis2 = "SELECT lpad(salemain.salemain,7,'0') AS 'รหัส',salemain.docdate AS 'วันที่',SUM(amount) AS 'ราคารวม',name AS 'ลูกค้า',concat(agentname,' ',agentlastname) AS 'ผู้ทำรายการ' "
        SelectHis2 += "FROM cpm.salemain LEFT JOIN cpm.sale ON salemain.SALEMAIN = sale.SALEMAIN LEFT JOIN cpm.mascustomer On mascustomer.idmascustomer = salemain.CUSID LEFT JOIN cpm.masagent ON masagent.AGENTID = salemain.AGENTID LEFT JOIN cpm.masproduct ON masproduct.idmasproduct = sale.PRODUCT "
        SelectHis2 += "WHERE salemain.docdate BETWEEN '" & date1.Year & "/" & date1.Month & "/" & date1.Day & "' AND '" & date2.Year & "/" & date2.Month & "/" & date2.Day & "' GROUP BY salemain.salemain;"
        Return SelectHis2
    End Function
    Property CODE_() As String
        Get
            Return CODE
        End Get
        Set(value As String)
            CODE = value
        End Set
    End Property
    Property USREQ_() As Integer
        Get
            Return USREQ
        End Get
        Set(value As Integer)
            USREQ = value
        End Set
    End Property
    Property IDMASPRODUCTTYPE_() As Integer
        Get
            Return IDMASPRODUCTTYPE
        End Get
        Set(value As Integer)
            IDMASPRODUCTTYPE = value
        End Set
    End Property
    Property IDMASPRODUCT_() As Integer
        Get
            Return IDMASPRODUCT
        End Get
        Set(value As Integer)
            IDMASPRODUCT = value
        End Set
    End Property
    Property REMARK_() As String
        Get
            Return REMARK
        End Get
        Set(value As String)
            REMARK = value
        End Set
    End Property
    Property PRODUCTNAME_() As String
        Get
            Return PRODUCTNAME
        End Get
        Set(value As String)
            PRODUCTNAME = value
        End Set
    End Property
    Property STATUS_() As Boolean
        Get
            Return STATUS
        End Get
        Set(value As Boolean)
            If value = True Then
                STATUS = 1
            Else
                STATUS = 0
            End If
        End Set
    End Property

    Property QTY_() As Double
        Get
            Return QTY
        End Get
        Set(value As Double)
            QTY = value
        End Set
    End Property
    Property BPRICE_() As Double
        Get
            Return BPRICE
        End Get
        Set(value As Double)
            BPRICE = value
        End Set
    End Property
    Property SPRICE_() As Double
        Get
            Return SPRICE
        End Get
        Set(value As Double)
            SPRICE = value
        End Set
    End Property
    Public Sub insertMasproduct2()
        connect = CONDB.NewConnection()
        connect.BeginTrans()
        Try
            Dim sql As String
            Dim lastid As Integer
            sql = "INSERT INTO cpm.masproduct"
            sql += "(`idmasproducttype`,`productname`,`bprice`,`sprice`,`remark`,`docdate`,`qty`,`code`)"
            sql += "VALUES ("
            sql += "'" & IDMASPRODUCTTYPE & "',"
            sql += "'" & PRODUCTNAME & "',"
            sql += "'" & BPRICE & "',"
            sql += "'" & SPRICE & "',"
            sql += "'" & REMARK & "',"
            sql += "current_timestamp(),"
            sql += "'" & QTY & "',"
            sql += "'" & CODE & "' );SELECT LAST_INSERT_ID()"
            lastid = connect.ExecuteScalar(sql)
            sql = "INSERT INTO cpm.hisbuy (`product`,`qty`,`usreq`,`date`,`bprice`) VALUES ('" & lastid & "','" & QTY & "','" & USREQ & "',current_timestamp,'" & BPRICE & "'); "
            connect.ExecuteNonQuery(sql)
            connect.CommitTrans()
            MsgBox("ท่านได้ทำการบันทึกข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
        Catch ex As Exception
            connect.RollbackTrans()
            MsgBox(ex.ToString)
        Finally
            connect.Dispose()
        End Try
    End Sub
    Public Sub editMasproduct2()
        connect = CONDB.NewConnection()
        connect.BeginTrans()
        Try
            Dim sql As String
            sql = "UPDATE cpm.masproduct SET"
            sql += "`idmasproducttype` = '" & IDMASPRODUCTTYPE & "',"
            sql += "`productname` = '" & PRODUCTNAME & "',"
            sql += "`bprice` = '" & BPRICE & "',"
            sql += "`sprice` = '" & SPRICE & "',"
            sql += "`qty` = '" & QTY & "',"
            sql += "`code` = '" & CODE & "'"
            sql += "WHERE `idmasproduct` = '" & IDMASPRODUCT & "';"
            connect.ExecuteNonQuery(sql)
            connect.CommitTrans()
            MsgBox("ท่านได้ทำการแก้ไขข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
        Catch ex As Exception
            connect.RollbackTrans()
            MsgBox(ex.ToString)
        Finally
            connect.Dispose()
        End Try

    End Sub
End Class
